﻿using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Newtonsoft.Json;
using System;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace _08_EF
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            //  Add();
            //  await InitBook();
            //  Select();
            //  await update();
            // await delete();
            // ExecSql();
        }


        static void ExecSql(double price=100)
        {
            // 返回多条
            System.FormattableString sql = $"SELECT * FROM T_Books WHERE Price={price}";
            using (MyDbContent db = new MyDbContent())
            {
                // 返回结果集
                var res=db.Books.FromSqlInterpolated(sql);
                Console.WriteLine(res.Count());
                foreach (var book in res)
                {
                    Console.WriteLine(JsonConvert.SerializeObject(book));
                }
               
                
            }
            
        }

        static async Task delete()
        {
            using (MyDbContent db = new MyDbContent())
            {
                // 单独删除
                var res = db.dogs.Single(e => e.Id == 2);
                db.dogs.Remove(res);
                await db.SaveChangesAsync();

                // 

            }
        }

        static async Task update()
        {
            using (MyDbContent db = new MyDbContent())
            {
               
                // 确定唯一的用Single,不然报错
                var res1 = db.Books.Single(e => e.AuthorName == "黄章");
                res1.AuthorName = "黄章001";

                var res2 = db.Books.Where(e => e.AuthorName == "Author");
                foreach (var item in res2) {
                    item.AuthorName = "妖精的尾巴";
                }
                await db.SaveChangesAsync();
            }
        }

        static void Select()
        {
            using (MyDbContent db = new MyDbContent())
            {
                var res = db.Books.Where(b => b.Price > 80);
                foreach (Book item in res)
                {
                    Console.WriteLine(item.Price);
                }

                var res1 = db.Books.Where(b => b.Title == "c#语言").FirstOrDefault();
                Console.WriteLine(JsonConvert.SerializeObject(res1));

                Console.WriteLine("排序..................");
                var res2 = db.Books.OrderBy(b => b.Price);
                foreach (Book item in res2)
                {
                    Console.WriteLine(JsonConvert.SerializeObject(item));
                }

                Console.WriteLine("分组。。。。。。。。。。。。。");
                var res3=db.Books.GroupBy(b => b.AuthorName)
                    .Select(g=>new { 
                            name=g.Key,
                            count=g.Count(),
                            maxPrice=g.Max(e=>e.Price),
                            sumPrice=g.Sum(e=>e.Price)
                            }
                        );

                foreach (var item in res3)
                {
                    Console.WriteLine(JsonConvert.SerializeObject(item));
                }

            }



        }
        

        static async Task InitBook()
        {
            using (MyDbContent db = new MyDbContent())
            {
                Book b1 = new Book
                {
                    AuthorName = "杨中科",
                    Title = "c#语言",
                    Price = 59.8,
                    PubTime = new DateTime(2019, 3, 1)
                };
                Book b2 = new Book
                {
                    AuthorName = "雷军",
                    Title = "小米",
                    Price = 99,
                    PubTime = new DateTime(2012, 10, 1)
                };
                Book b3 = new Book
                {
                    AuthorName = "黄章",
                    Title = "魅族",
                    Price = 69,
                    PubTime = new DateTime(2020, 5, 1)
                };
                Book b4 = new Book
                {
                    AuthorName = "杨中科",
                    Title = "sql今典",
                    Price = 52,
                    PubTime = new DateTime(2008, 9, 1)
                };
                Book b5 = new Book
                {
                    AuthorName = "雷军",
                    Title = "造车",
                    Price = 990000,
                    PubTime = new DateTime(2020, 12, 12)
                };
                db.Books.Add(b1);
                db.Books.Add(b2);
                db.Books.Add(b3);
                db.Books.Add(b4);
                db.Books.Add(b5);

                await db.SaveChangesAsync();
                Console.WriteLine("1111");
            }
            Console.WriteLine("222");
        }

        static async void Add()
        {
            // 增加
            using (MyDbContent db = new MyDbContent())
            {
                Dog d = new Dog();
                d.Name = "bbbb";
                db.dogs.Add(d);  // 把d 对象加入到dogs整个逻辑表里


                Dog d2 = new Dog();
                d2.Name = "aaa";
                db.dogs.Add(d2);

                Book book = new Book();
                book.AuthorName = "Author";
                book.Price = 100;
                book.PubTime = DateTime.Now;
                book.Title = "Title";
                db.Books.Add(book);


                await db.SaveChangesAsync(); // 异步 相当于update-database命令

            }
        }
    }
}
